package shop.data;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.sql.PreparedStatement;

import shop.business.*;

public class ProductDB {
	public static ArrayList<Product> listProduct(){
		ArrayList<Product> lp=new ArrayList<Product>();
		ConnectionPool pool=ConnectionPool.getInstance();
		Connection con=pool.getConnection();
		ResultSet rs=null;
		Statement st=null;
		try {
			String Query="Select * From Product";
			st=con.createStatement();
			rs=st.executeQuery(Query);
			while(rs.next()){
				Product p=new Product();
				p.setProductCode(rs.getString("ProductCode"));
				p.setPrice(rs.getDouble("ProductPrice"));
				p.setProductDescription(rs.getString("ProductDescription"));
				lp.add(p);
			}		
			return lp;
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
			return null;
		}
		finally{			
			DBUlti.closeStatement(st);
			DBUlti.closeResultSet(rs);
			pool.freeConnection(con);
		}		
	}
	public static Product selectProduct(String productCode){
		ConnectionPool pool=ConnectionPool.getInstance();
		Connection con=pool.getConnection();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String Query="Select * from Product Where ProductCode= ? ";
			ps=con.prepareStatement(Query);
			ps.setString(1, productCode);
			rs=ps.executeQuery();
			if(rs.next()){
				Product p=new Product();
				p.setProductCode(rs.getString("ProductCode"));
				p.setPrice(rs.getDouble("ProductPrice"));
				p.setProductDescription(rs.getString("ProductDescription"));
				return p;
			}
			else{
				return null;
			}
		} catch (SQLException sqle) {
			// TODO: handle exception
			sqle.printStackTrace();
			return null;			
		}
		finally{
			DBUlti.closePreparedStatement(ps);
			DBUlti.closeResultSet(rs);
			pool.freeConnection(con);
		}
	}
}
